﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace Glossary
{
    public static class IODatabase
    {
        private static string _connectionString = string.Empty;

        private static void UpdateConnectionString()
        {
            using (StreamReader sr = new StreamReader(@"..\..\..\Glossary\bin\debug\ConnectionString.txt", Encoding.Unicode))
            {
                _connectionString = sr.ReadLine();
            }
        }

        public static CreatedTest ReadCreatedTestFromDataSource(string createdTestID)
        {
            CreatedTest createdTest;
            UpdateConnectionString();

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                con.Open();
                string commandText = string.Format("SELECT * FROM CreatedTestsView WHERE CreatedTestID = {0}", createdTestID);
                using (SqlCommand cmd = new SqlCommand(commandText, con))
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    reader.Read();
                    createdTest = new CreatedTest(int.Parse(reader[3].ToString()));
                    createdTest.CreatedTestID = (int)reader[0];
                    createdTest.DateOfCreation = DateTime.Parse(reader[1].ToString());
                    createdTest.LanguageOfTest = (Language)Enum.Parse(typeof(Language), reader[4].ToString());
                }
            }

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                con.Open();
                string commandText = string.Format("SELECT TestWords.WordID, SwedishWordText, Description " +
                                                   "FROM SwedishWords " +
                                                   "JOIN TestWords ON SwedishWords.WordID = TestWords.WordID " +
                                                   "WHERE CreatedTestID = {0} " +
                                                   "ORDER BY SwedishWords.WordID", createdTestID);
                using (SqlCommand cmd = new SqlCommand(commandText, con))
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    for (int i = 0; i < createdTest.NumberOfWords; i++)
                    {
                        reader.Read();
                        createdTest.Words[i].WordID = reader.GetInt32(0);
                        createdTest.Words[i].SwedishWord = reader.GetString(1);
                        createdTest.Words[i].SwedishDescription = reader.GetString(2);
                    }
                }
            }
            return createdTest;
        }

        public static FilledTest ReadFilledTestFromDataSource(string filledTestID)
        {
            FilledTest filledTest;
            UpdateConnectionString();

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                con.Open();
                string commandText = string.Format("SELECT * " +
                               "FROM FilledTestsView " +
                               "WHERE FilledTestID = {0}", filledTestID);
                using (SqlCommand cmd = new SqlCommand(commandText, con))
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    reader.Read();
                    filledTest = new FilledTest(ReadCreatedTestFromDataSource(reader[1].ToString()));
                    filledTest.FilledTestID = Convert.ToInt32(filledTestID);
                    filledTest.DateOfTest = DateTime.Parse(reader[2].ToString());
                    filledTest.Student.SocialSecurityNumber = reader[4].ToString();
                    filledTest.Student.FirstName = reader[5].ToString();
                    filledTest.Student.LastName = reader[6].ToString();
                    filledTest.Student.Adress.Street = reader[7].ToString();
                    filledTest.Student.Adress.ZipCode = reader[8].ToString();
                    filledTest.Student.Adress.City = reader[9].ToString();
                    filledTest.Student.Adress.Country = reader[10].ToString();
                }
            }

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                con.Open();
                string commandText = string.Format("SELECT Answer " +
                                                   "FROM Answers " +
                                                   "WHERE FilledTestID = {0} " +
                                                   "ORDER BY WordID", filledTestID);
                using (SqlCommand cmd = new SqlCommand(commandText, con))
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    for (int i = 0; i < filledTest.NumberOfWords; i++)
                    {
                        reader.Read();
                        filledTest.StudentAnswers[i] = reader[0].ToString();
                    }
                }
            }
            return filledTest;
        }

        public static CorrectedTest ReadCorrectedTestFromDataSource(string correctedTestID)
        {
            CorrectedTest correctedTest;
            UpdateConnectionString();

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                con.Open();
                string commandText = string.Format("SELECT * FROM CorrectedTestsView WHERE CorrectedTestID = {0}", correctedTestID);
                using (SqlCommand cmd = new SqlCommand(commandText, con))
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    reader.Read();
                    correctedTest = new CorrectedTest(ReadFilledTestFromDataSource(reader[1].ToString()));
                    correctedTest.DateOfCorrection = DateTime.Parse(reader[2].ToString());
                    correctedTest.CaseSensitiveCorrection = (reader[3].ToString() == "1");
                    correctedTest.Score = Convert.ToInt32(reader[4].ToString());
                }
            }
            return correctedTest;
        }

        public static void WriteCreatedTestToDataSource(CreatedTest createdTest)
        {
            UpdateConnectionString();
            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                con.Open();
                string commandText = string.Format("INSERT INTO CreatedTests VALUES(@DateOfCreation, @CourseID, @NbrOfQuestions)");
                using (SqlCommand command = new SqlCommand(commandText, con))
                {
                    command.Parameters.Add(new SqlParameter("DateOfCreation", createdTest.DateOfCreation.ToShortDateString()));
                    command.Parameters.Add(new SqlParameter("CourseID", createdTest.CourseID));
                    command.Parameters.Add(new SqlParameter("NbrOfQuestions", createdTest.NumberOfWords));
                    command.ExecuteNonQuery();
                }
            }

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                con.Open();
                string commandText = string.Format("SELECT TOP 1 CreatedTestID " +
                                                   "FROM CreatedTests " +
                                                   "WHERE DateOfCreation = '{0}' " +
                                                   "AND CourseID = {1} " +
                                                   "AND NbrOfQuestions = {2} " +
                                                   "ORDER BY CreatedTestID DESC",
                                                   createdTest.DateOfCreation.ToShortDateString(),
                                                   createdTest.CourseID,
                                                   createdTest.NumberOfWords);
                using (SqlCommand cmd = new SqlCommand(commandText, con))
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    reader.Read();
                    createdTest.CreatedTestID = reader.GetInt32(0);
                }
            }

            for (int i = 0; i < createdTest.NumberOfWords; i++)
            {
                using (SqlConnection con = new SqlConnection(_connectionString))
                {
                    con.Open();
                    string commandText = string.Format("INSERT INTO TestWords VALUES(@CreatedTestID, @WordID)");
                    using (SqlCommand command = new SqlCommand(commandText, con))
                    {
                        command.Parameters.Add(new SqlParameter("CreatedTestID", createdTest.CreatedTestID));
                        command.Parameters.Add(new SqlParameter("WordID", createdTest.Words[i].WordID));
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        public static void WriteFilledTestToDataSource(FilledTest filledTest)
        {
            UpdateConnectionString();

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                con.Open();
                string commandText = string.Format("SELECT StudentID " +
                                                   "FROM Students " +
                                                   "WHERE SocialSecurityNumber = '{0}'", filledTest.Student.SocialSecurityNumber);
                using (SqlCommand cmd = new SqlCommand(commandText, con))
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        filledTest.Student.StudentID = reader.GetInt32(0);
                    }
                    else
                    {
                        filledTest.Student.StudentID = 0;
                    }
                }
            }

            if (filledTest.Student.StudentID == 0)
            {
                using (SqlConnection con = new SqlConnection(_connectionString))
                {
                    con.Open();
                    string commandText = string.Format("INSERT INTO Students VALUES(@SocialSecurityNumber, " +
                                                       "@Firstname, " +
                                                       "@Lastname, " +
                                                       "@Adress, " +
                                                       "@PostalCode, " +
                                                       "@City, " +
                                                       "@Country, " +
                                                       "@Phone, " +
                                                       "@Email)");
                    using (SqlCommand command = new SqlCommand(commandText, con))
                    {
                        command.Parameters.Add(new SqlParameter("SocialSecurityNumber", filledTest.Student.SocialSecurityNumber));
                        command.Parameters.Add(new SqlParameter("Firstname", filledTest.Student.FirstName));
                        command.Parameters.Add(new SqlParameter("Lastname", filledTest.Student.LastName));
                        command.Parameters.Add(new SqlParameter("Adress", filledTest.Student.Adress.Street));
                        command.Parameters.Add(new SqlParameter("PostalCode", filledTest.Student.Adress.ZipCode));
                        command.Parameters.Add(new SqlParameter("City", filledTest.Student.Adress.City));
                        command.Parameters.Add(new SqlParameter("Country", filledTest.Student.Adress.Country));
                        command.Parameters.Add(new SqlParameter("Phone", filledTest.Student.Adress.City));
                        command.Parameters.Add(new SqlParameter("Email", filledTest.Student.Adress.Country));
                        command.ExecuteNonQuery();
                    }
                }
            }

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                con.Open();
                string commandText = string.Format("SELECT StudentID " +
                                                   "FROM Students " +
                                                   "WHERE SocialSecurityNumber = '{0}'", filledTest.Student.SocialSecurityNumber);
                using (SqlCommand cmd = new SqlCommand(commandText, con))
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    reader.Read();
                    filledTest.Student.StudentID = reader.GetInt32(0);
                }
            }

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                con.Open();
                string commandText = string.Format("INSERT INTO FilledTests VALUES(@DateOfTest, @CreatedTestID, @StudentID);SELECT @@Identity");
                using (SqlCommand command = new SqlCommand(commandText, con))
                {
                    command.Parameters.Add(new SqlParameter("DateOfTest", filledTest.DateOfTest.ToShortDateString()));
                    command.Parameters.Add(new SqlParameter("CreatedTestID", filledTest.CreatedTestID));
                    command.Parameters.Add(new SqlParameter("StudentID", filledTest.Student.StudentID.ToString()));
                    filledTest.FilledTestID = int.Parse(command.ExecuteScalar().ToString());
                }
            }

            for (int i = 0; i < filledTest.NumberOfWords; i++)
            {
                using (SqlConnection con = new SqlConnection(_connectionString))
                {
                    con.Open();
                    string commandText = string.Format("INSERT INTO Answers VALUES(@FilledTestID, @WordID, @Answer, @Correct)");
                    using (SqlCommand command = new SqlCommand(commandText, con))
                    {
                        command.Parameters.Add(new SqlParameter("FilledTestID", filledTest.FilledTestID));
                        command.Parameters.Add(new SqlParameter("WordID", filledTest.Words[i].WordID));
                        command.Parameters.Add(new SqlParameter("Answer", filledTest.StudentAnswers[i]));
                        command.Parameters.Add(new SqlParameter("Correct",
                            (String.Compare(filledTest.Words[i].ForeignWord, filledTest.StudentAnswers[i], true) == 0) ? "1" : "0"));
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        public static void WriteCorrectedTestToDataSource(CorrectedTest correctedTest)
        {
            UpdateConnectionString();
            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                con.Open();
                string commandText = string.Format("INSERT INTO CreatedTests VALUES(@DateOfCorrection, @FilledTestID, @CaseSensitive, @Score)");
                using (SqlCommand command = new SqlCommand(commandText, con))
                {
                    command.Parameters.Add(new SqlParameter("DateOfCorrection", correctedTest.DateOfCorrection.ToShortDateString()));
                    command.Parameters.Add(new SqlParameter("FilledTestID", correctedTest.FilledTestID));
                    command.Parameters.Add(new SqlParameter("CaseSensitive", correctedTest.CaseSensitiveCorrection ? "1" : "0"));
                    command.Parameters.Add(new SqlParameter("Score", correctedTest.Score));
                    command.ExecuteNonQuery();
                }
            }
        }

        public static string[] GetTestIDsFromDataSource()
        {
            List<string> result = new List<string>();
            UpdateConnectionString();
            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                con.Open();
                string commandText = string.Format("SELECT CreatedTestID FROM CreatedTests");
                using (SqlCommand cmd = new SqlCommand(commandText, con))
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    for (int i = 0; reader.Read(); i++)
                    {
                        result.Add(reader[0].ToString());
                    }
                }
            }
            return result.ToArray();
        }

        public static void ReadGlossaryFromDataSource(ref WordLibrary wordLibrary, string lang)
        {
            Language language = (Language)Enum.Parse(typeof(Language), lang);
            Word word;

            UpdateConnectionString();
            using (SqlConnection sqlConnection1 = new SqlConnection(_connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader;

                cmd.CommandText = string.Format("EXEC ReadWordLibraryProc '{0}'", lang);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    word = new Word(int.Parse(reader[0].ToString()), reader[2].ToString().Trim(), reader[1].ToString().Trim(), reader[4].ToString().Trim(), language);
                    wordLibrary.AddWord(word);
                }

            }
        }

        public static List<string> ReadCoursesFromDataSource()
        {
            UpdateConnectionString();

            using (SqlConnection sqlConnection1 = new SqlConnection(_connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader;

                cmd.CommandText = string.Format("SELECT CourseID FROM Courses");
                cmd.CommandType = CommandType.Text;
                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();
                reader = cmd.ExecuteReader();

                List<string> courses = new List<string>();
                int i = 0;
                while (reader.Read())
                {
                    courses.Add(reader[0].ToString().Trim());
                    i++;
                }

                return courses;
            }
        }

        public static void WriteGlossaryToFile(List<Word> wordList, Language language, string fileName)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Ansvar: Kim Nielsen
        /// </summary>
        /// <param name="word"></param>
        public static void WriteNewWordToDataSource(Word word)
        {
            UpdateConnectionString();

            using (SqlConnection sqlConnection1 = new SqlConnection(_connectionString))
            {
                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = string.Format("AddWordsProc '{0}', '{1}', '{2}', '{3}'", word.ForeignLanguage, word.ForeignWord, word.SwedishWord, word.SwedishDescription);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();

                int count = cmd.ExecuteNonQuery();

            }
        }
    }
}
